package com.sample.javabean;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.sample.action.form.MenuForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 定义菜单模型
 *
 */
public class Menu {
	
	private static final Log LOG=LogFactory.getLog(Menu.class);
	
	private Integer id;
	private Integer pid;
	private String menuname;
	private String menuurl;
	private String remark;
	private String type;//菜单类型
	private String pname;
	// 实例化JdbcUtils组件，完成加载驱动以及连接数据库操作，获得连接实例
	private JdbcUtils jdbcUtils = new JdbcUtils();
	
	
	
	public Menu(Integer id){
		this.id =id;
	}
	/**
	 * 根据主键加载菜单信息
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_menu where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.id =rs.getInt("id");
				this.pid =rs.getInt("pid");
				this.menuname =rs.getString("menuname");
				this.menuurl =rs.getString("menuurl");
				this.remark =rs.getString("remark");
				this.type =rs.getString("type");
			}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载菜单信息失败！");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	/**
	 * 添加菜单
	 */
	public void addMenu() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "insert into t_menu(pid,menuname,menuurl,remark,type) values('"
					+ this.pid + "','" + this.menuname + "','" + this.menuurl + "','"+this.remark+"','"+this.type+"')";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void editMenu() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "update  t_menu set pid='"+this.pid+"',menuname='"+this.menuname+"',menuurl='"+this.menuurl+"',remark='"+this.remark+"',type='"+this.type+"' where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	public void delMenu() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_menu  where id='"+this.id+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 分页显示菜单信息
	 * @return
	 */
	public Pager<Menu> list(int page,int size,MenuForm menuForm,String keywords) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<Menu> menus=new Pager<Menu>();
		menus.setPage(page);
		menus.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_menu where 1=1";
			String selectSql1="select * from t_menu t LEFT JOIN (select id,menuname pname from t_menu) a on t.pid = a.id  where 1=1";
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//模糊查询
			if(null != keywords && "" != keywords && !"".equals(keywords)){
				selectSql1 = selectSql1+"   and menuname like '%"+keywords+"%'";
			}
			
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 menus.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(selectSql1);
			 while(rs.next()){
			
				 int id=rs.getInt("id");
				  int pid=rs.getInt("pid");
				  String menuname=rs.getString("menuname");
				  String menuurl=rs.getString("menuurl");
				  String remark=rs.getString("remark");
				  String type=rs.getString("type");
				  String pname=rs.getString("pname");
				  Menu menu=new Menu(id, pid,menuname, menuurl, remark,type);
				  menu.setPname(pname);
				  menus.getDataSets().add(menu);
			 }
			 menus.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return menus;

	}
	
	/**
	 * 查找根菜单
	 * @return
	 */
	public List<Menu> findAll(){
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Menu> rootMenuList =new ArrayList<Menu>(20);
		Menu rootMenu=new Menu();
		rootMenu.setId(0);
		rootMenu.setMenuname("根菜单");
		//把根菜单放进列表
		rootMenuList.add(rootMenu);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			String sql="select * from t_menu where 1=1 and type = 0";
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt("id");
				String menuname =rs.getString("menuname");
				Menu model=new Menu();
				model.setId(id);
				model.setMenuname(menuname);
				//把根菜单放进列表
				rootMenuList.add(model);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			LOG.error("load menu list failed!", e);
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return rootMenuList;
		
	}
	
	
	
	/**
	 * 查找菜单列表
	 * @return
	 */
	public List<Menu> findMenuList(){
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Menu> menuList =new ArrayList<Menu>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			String sql="select * from t_menu where 1=1 and type = 1";
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt("id");
				String menuname =rs.getString("menuname");
				Menu model=new Menu();
				model.setId(id);
				model.setMenuname(menuname);
				//把根菜单放进列表
				menuList.add(model);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			LOG.error("load menu list failed!", e);
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return menuList;
		
	}
	
	//定义无参构造函数
	public Menu(){
		
	}
	public Menu(Integer id, Integer pid, String menuname, String menuurl,
			String remark, String type) {
		super();
		this.id = id;
		this.pid = pid;
		this.menuname = menuname;
		this.menuurl = menuurl;
		this.remark = remark;
		this.type = type;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public Integer getPid() {
		return pid;
	}
	public void setPid(Integer pid) {
		this.pid = pid;
	}
	public String getMenuname() {
		return menuname;
	}
	public void setMenuname(String menuname) {
		this.menuname = menuname;
	}
	public String getMenuurl() {
		return menuurl;
	}
	public void setMenuurl(String menuurl) {
		this.menuurl = menuurl;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
	public String getPname() {
		return pname;
	}
	public void setPname(String pname) {
		this.pname = pname;
	}
	
}
